﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;

namespace NaK.Core
{
    public class SqlFast
    {
        public SqlFast(CommandType? defaultCommandType = null, string connectionString = null)
        {
            DefaultCommandType = defaultCommandType ?? CommandType.StoredProcedure;
            if (connectionString != null) SqlConnection = new SqlConnection(connectionString);
        }

        public SqlConnection SqlConnection { get; set; }
        public CommandType DefaultCommandType { get; set; }

        public int ExecuteNonQuery(string sqlStatement, Dictionary<string, object> parameterNamesAndValues = null, CommandType? commandType = null)
        {
            using (SqlConnection sqlConnection = SqlConnection ?? SqlConnections.DefaultSqlConnection)
            {
                using (SqlCommand sqlCommand = new SqlCommand(sqlStatement, sqlConnection) { CommandType = commandType ?? DefaultCommandType })
                {
                    if (parameterNamesAndValues != null)
                    {
                        foreach (KeyValuePair<string, object> parameterNameAndValue in parameterNamesAndValues)
                        {
                            sqlCommand.Parameters.AddWithValue(parameterNameAndValue.Key, parameterNameAndValue.Value);
                        }
                    }
                    sqlCommand.Connection.Open();
                    return sqlCommand.ExecuteNonQuery();
                }
            }
        }

        public Dictionary<string, object> GetOneRow(string sqlStatement, Dictionary<string, object> parameterNamesAndValues = null, CommandType? commandType = null)
        {
            Dictionary<string, object> columnNamesAndValues = new Dictionary<string, object>();
            using (SqlConnection sqlConnection = SqlConnection ?? SqlConnections.DefaultSqlConnection)
            {
                using (SqlCommand sqlCommand = new SqlCommand(sqlStatement, sqlConnection) { CommandType = commandType ?? DefaultCommandType })
                {
                    if (parameterNamesAndValues != null)
                    {
                        foreach (KeyValuePair<string, object> parameterNameAndValue in parameterNamesAndValues)
                        {
                            sqlCommand.Parameters.AddWithValue(parameterNameAndValue.Key, parameterNameAndValue.Value);
                        }
                    }
                    sqlCommand.Connection.Open();
                    SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
                    if (sqlDataReader.HasRows)
                    {
                        sqlDataReader.Read();
                        for (int i = 0; i < sqlDataReader.FieldCount; i++)
                        {
                            columnNamesAndValues.Add(sqlDataReader.GetName(i), SqlDbObjectToObject(sqlDataReader[i]));
                        }
                    }
                }
            }
            return columnNamesAndValues;
        }

        private void AddParametersToSqlCommand(SqlCommand sqlCommand, Dictionary<string, object> parameterNamesAndValues)
        {
            foreach (KeyValuePair<string, object> parameterNameAndValue in parameterNamesAndValues)
            {
                sqlCommand.Parameters.AddWithValue(parameterNameAndValue.Key, parameterNameAndValue.Value);
            }
        }
        private object SqlDbObjectToObject(object obj)
        {
            return (obj is DBNull) ? null : obj;
        }

        public DataSet GetDataSet(string sqlStatement, Dictionary<string, object> parameterNamesAndValues = null, CommandType? commandType = null)
        {
            DataSet dataSet = new DataSet();
            using (SqlConnection sqlConnection = SqlConnection ?? SqlConnections.DefaultSqlConnection)
            {
                using (SqlCommand sqlCommand = new SqlCommand(sqlStatement, sqlConnection) { CommandType = commandType ?? DefaultCommandType })
                {
                    if (parameterNamesAndValues != null) AddParametersToSqlCommand(sqlCommand, parameterNamesAndValues);
                    using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand))
                    {
                        sqlDataAdapter.Fill(dataSet);
                    }
                }
            }
            return dataSet;
        }
    }
}
